/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package snAnalysis;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author FUJISAI
 */
public class MySQLBridge {

    private Connection conn;
    private Statement stm;
    private String db_host;
    private String db_name;
    private String db_user;
    private String db_pass;

    public MySQLBridge() {
        try {            
            this.setParameters();

            Class c = Class.forName("com.mysql.jdbc.Driver");
            String db_url = "jdbc:mysql://" + this.db_host + ":3306/" + this.db_name;
            this.conn = DriverManager.getConnection(db_url, this.db_user, this.db_pass);

            this.stm = this.conn.createStatement();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } 
    }

    public MySQLBridge(String initQuery) {
        try {            

            this.setParameters();

            Class c = Class.forName("com.mysql.jdbc.Driver");
            String db_url = "jdbc:mysql://" + this.db_host + ":3306/" + this.db_name;
            this.conn = DriverManager.getConnection(db_url, this.db_user, this.db_pass);

            this.stm = this.conn.createStatement();
            this.stm.executeUpdate(initQuery);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } 
    }

    public void setParameters() {
        this.db_host = Configuration.getValue("host", "localhost");
        this.db_name = Configuration.getValue("db_name", "java_mysql");
        this.db_user = Configuration.getValue("db_user", "webmin");
        this.db_pass = Configuration.getValue("db_pass", "abc123");
    }

    public void closeConnection() {
        try {
            this.conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 
     * @return
     */

    public ResultSet getSingleField(String table_name, String field, String where_phrase) {
        try {
            String query = "";
            if (where_phrase.compareTo("") != 0) {
                query = "SELECT `" + field + "` FROM `" + table_name + "` WHERE "
                    + where_phrase;
            } else {
                query = "SELECT `" + field + "` FROM `" + table_name + "`";
            }
            ResultSet resl = this.stm.executeQuery(query);
            return resl;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public ResultSet getAllField(String table_name, String where_phrase) {
        try {
            String query = "";
            if (where_phrase.compareTo("") != 0) {
                query = "SELECT * FROM `" + table_name + "` WHERE " + where_phrase;
            }
            else {
                query = "SELECT * FROM `" + table_name + "`";
            }

            ResultSet result = this.stm.executeQuery(query);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     *
     * @param table_name
     * @param col1
     * @param col1_val
     * @param col2
     * @param col2_val
     */
    public void addToDB(String table_name, String col1, String col1_val, String col2, String col2_val) {
        try {
            ResultSet rel = this.customQuery("SELECT COUNT(*) AS TOTAL FROM `" + table_name + "` WHERE `blogID`='" + col1_val + "'");

            int total_rec = 0;
            while(rel.next()) {
                total_rec = Integer.parseInt(rel.getString("TOTAL"));
            }

            if (total_rec == 0) {
                String query = "INSERT INTO `" + table_name + "` (`" + col1 + "`,`" + col2 + "`) VALUES" +
                    " ('" + col1_val + "', '" + col2_val + "')";

                this.stm.executeUpdate(query);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 
     * @param table_name name of table in database
     * @param field
     * @param value
     * @param cond
     * @param condVal
     */
    public void updateDB(String table_name, String field, String value, String where_phrase) {
        String query = "UPDATE `" + table_name + "` SET `" + field + "`='" + value + "' WHERE " + where_phrase;
        //System.out.println(query);
        try {
            this.stm.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * @param table_name name of table in database
     * @param cond column name to make condition of query
     * @param condVal value of condition
     */
    public void removeFromDB(String table_name, String where_phrase) {
        try {
            String query = "DELETE FROM `" + table_name + "` WHERE " + where_phrase;

            this.stm.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void truncateTable(String table_name) {
        try {
            String query = "TRUNCATE TABLE `" + table_name + "`";

            this.stm.executeQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public ResultSet customQuery(String query) {
        try {
            ResultSet rsl = this.stm.executeQuery(query);

            return rsl;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void customUpdate(String query) {
        try {
            this.stm.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
